package com.my.jdbc.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SQLUtil {

    private static final Logger logger = LoggerFactory.getLogger(SQLUtil.class);

    private static final String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String MYSQL_URL = "jdbc:mysql://192.168.80.101:3308/db_device";
    private static final String MYSQL_USERNAME = "root";
    private static final String MYSQL_PASSWORD = "root";

    private static DruidDataSource dataSource;
    static {
        dataSource = new DruidDataSource();
        dataSource.setDriverClassName(MYSQL_DRIVER);
        dataSource.setUrl(MYSQL_URL);
        dataSource.setUsername(MYSQL_USERNAME);
        dataSource.setPassword(MYSQL_PASSWORD);
        dataSource.setInitialSize(5);
        dataSource.setMaxActive(20);
        dataSource.setMinIdle(5);
        dataSource.setMaxActive(60000);
    }

    public static List<Object[]> selectAll(String tableName){
        DruidPooledConnection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Object[]> result = new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            String sql = "SELECT * FROM "+tableName;
            resultSet = statement.executeQuery(sql);
            if (resultSet != null) {
                int columnCount = resultSet.getMetaData().getColumnCount();
                while (resultSet.next()){
                    Object[] row = new Object[columnCount];
                    for (int i = 0; i < columnCount; i++) {
                        row[i] = resultSet.getObject(i+1);
                    }
                    result.add(row);
                }
            }else{
                logger.info("查询结果为空");
            }
        }catch (Exception e){
            logger.error("JDBC执行出现异常：{}",e.getMessage(),e);
        }finally {
            try{
                if (resultSet!=null)resultSet.close();
                if (statement!=null)statement.close();
                if (connection!=null)connection.close();
            }catch (Exception e){
                logger.error("连接关闭失败");
            }
        }
        return result;
    }
}
